package com.csmy.my.center.util.report.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.reader.ReaderBuilder;
import net.sf.jxls.reader.XLSReadStatus;
import net.sf.jxls.reader.XLSReader;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;

import com.csmy.my.center.module.MyOrderInfo;
import com.csmy.my.center.module.OrderInfo;
import com.csmy.my.center.util.StringUtil;

public class JxlsUtil {

	/** 
	 * 导出excel 
	 * @param templateFile - excel模版名称 
	 * @param beans - 模版中填充的数据 
	 * @param os - 生成模版输出流 
	 */
	public static boolean export2Excel(Map beans, String srcPath,
			OutputStream os) {
		boolean temp = false;
		XLSTransformer transformer = new XLSTransformer();
		try {
			//获得模板的输入流
			FileInputStream in = new FileInputStream(srcPath);
			//将beans通过模板输入流写到workbook中
			Workbook workbook = transformer.transformXLS(in, beans);
			//将workbook中的内容用输出流写出去
			workbook.write(os);
			temp = true;
			StringUtil.xprint("导出数据成功...");
		} catch (Exception e) {
			temp = false;
			StringUtil.xprint("导出数据失败...");
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return temp;
	}

	public List excel2List(String xlsPath,String xmlConfig) {
		//path 要读的Excel的路径 
		//String xmlConfig = "orderTemplate.xml"; //xml文件名 
		try {
			InputStream inputXML = new BufferedInputStream(getClass().getResourceAsStream(xmlConfig));  
			XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
			InputStream inputXLS = new BufferedInputStream(getClass().getResourceAsStream(xlsPath));  
			MyOrderInfo orderInfo = new MyOrderInfo();
			List orderList = new ArrayList();
			Map beans = new HashMap();
			beans.put("orderInfo", orderInfo);
			beans.put("orderList", orderList);
			mainReader.read(inputXLS, beans);
			return orderList;
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}
	
	 public MyOrderInfo parseExcelFileToBeans(String xlsFile, String jxlsConfigFile)throws Exception {
		XLSReader xlsReader = ReaderBuilder.buildFromXML(new File(jxlsConfigFile));
		List orderList = new ArrayList();
		MyOrderInfo orderInfo = new MyOrderInfo();
		Map beans = new HashMap();
		beans.put("myOrder", orderInfo);
		beans.put("orderList", orderList);
		InputStream inputStream = null;
		try {
			inputStream = new BufferedInputStream(new FileInputStream(new File(xlsFile)));
			XLSReadStatus readStatus = xlsReader.read(inputStream, beans);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (inputStream != null) {
				inputStream.close();
			}
		}
		return orderInfo;
	}

	public static void main(String[] args) throws ParsePropertyException,
			InvalidFormatException, IOException {

		String templateFile = JxlsUtil.class.getClassLoader().getResource("").getPath();
		//OutputStream os = new FileOutputStream(templateFile + "template/outorder.xls");
		//templateFile += "template/orderTemplate.xls";
		StringUtil.xprint("path=" + templateFile);
/*		Map<String, Object> beans = new HashMap<String, Object>();

		// fruits  
		List<Map<String, String>> fruitList = new ArrayList<Map<String, String>>();

		Map<String, String> fruit = null;
		fruit = new HashMap<String, String>();
		fruit.put("name", "苹果");
		fruit.put("price", "100");
		fruitList.add(fruit);

		fruit = new HashMap<String, String>();
		fruit.put("name", "香蕉");
		fruit.put("price", "200");
		fruitList.add(fruit);

		beans.put("fruits", fruitList);
		export2Excel(beans, templateFile, os);*/
		String xlsPath = templateFile + "template/outorder.xls";
		String xmlPath = templateFile + "template/orderTemplate.xml";
		/*JxlsUtil jUtil = new JxlsUtil();
		List<OrderInfo> oList = jUtil.excel2List(xlsPath, xmlPath);
		for (OrderInfo order : oList) {
			StringUtil.xprint(order.getOrder_id());
		}
		
		ExcelReader eReader = new ExcelReader(null,new FileInputStream(xlsPath));
		try {
			eReader.read(4);
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
		    JxlsUtil jUtil = new JxlsUtil();
	        List<OrderInfo> oList;
	        MyOrderInfo orderInfo;
			try {
				orderInfo = jUtil.parseExcelFileToBeans(xlsPath,xmlPath);
				StringUtil.xprint(orderInfo.getZds()+"--"+orderInfo.getZje()+"--"+orderInfo.getZyf());
				oList = orderInfo.getOrder();
				for (OrderInfo order : oList) {
					StringUtil.xprint(order.getOrder_id()+"--"+order.getCourier_id()+"-"+order.getBuyer_phone());
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
	}
}
